import pymysql
from todo import Todo

class ToDoManagerMysql:
    @staticmethod
    def get_connection():
        return pymysql.connect(host='192.168.195.129',database='test',user='root',password='root@123ABC',autocommit=True)

    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''CREATE TABLE if not EXISTS todos(
            todo_id INT not null PRIMARY KEY auto_increment,
            title VARCHAR(50) not null DEFAULT '',
            completed INT not null DEFAULT 0
            )''')


    def create(self,title:str):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('insert into todos(title) values(%s);',(title,))
            cursor.execute('select last_insert_id();')
            (todo_id,) = cursor.fetchone()
            return Todo(title,id=todo_id)

    def convert_row_to_object(self,row):
        return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def list_todos(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos;')
            todo_list = cursor.fetchall()
            print(todo_list)
            obj_list = []
            for row in todo_list:

                obj_list.append(self.convert_row_to_object(row))
            return obj_list

    def get_todo_byid(self,id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos where todo_id=%s;',(id,))
            data = cursor.fetchone()

            if data is not None:
                return self.convert_row_to_object(data)
            else:
                pass


    def mark_completed(self,id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('UPDATE todos set completed=1 where todo_id=%s', (id,))
            return self.get_todo_byid(id)

    # def mark_completed(self,id):
    #
    #     with self.get_connection() as conn:
    #         cursor = conn.cursor()
    #         cursor.execute('select todo_id from todos where todo_id=%s;',(id,))
    #         data = cursor.fetchone()
    #         for i in data:
    #             cursor.execute('UPDATE todos set completed=1 where todo_id=%s',(i,))



manger = ToDoManagerMysql()
manger.init_db()
manger.mark_completed(3)
